﻿---4. GUARANTEE  AND VOLUME

				--4.1. BAO LANH

DECLARE @DATE NVARCHAR (20) 
SET @DATE='201605' 


DECLARE @DATE_NS NVARCHAR (20)
SET @DATE_NS='201604' ----------BANG NHAN SU CO MUON NEN UPDATE THANG TRUOC

DECLARE @DATE0 NVARCHAR (20)
SET @DATE0='2016-05-01' 

	DECLARE @DATE1 NVARCHAR (20)
	SET @DATE1='2016-05-31' 

--SELECT * FROM [ANHCP].[MBO_SBO].[dbo].[HR_file_201601]
print('1')
EXEC ('SELECT  BUSINESS_DATE,MD_ID,CUST_ID,VALUE_DATE,MUC_DICH,CUS_SEGMENT,ORG_AMOUNT*MID_RATE AS AMOUNT_QD,
        C.DAO AS DAO_CIF,D.[Tên nhân viên],BRANCH_NAME_SME,ZONE_ID_SME,CO_CODE,CATEGORY,FUND_PROGRAM INTO MD_A
  FROM SERVER16.[VPB_WHR2].[DBO].[MD_RPT_DAILY_VPB] A
  LEFT JOIN  SERVER16.VPB_WHR2.DBO.FOCURR_SAVE B
  ON A.CCY=B.CODE
  LEFT JOIN SERVER74.BICDATA.DBO.CUSTOMER C
  ON A.CUST_ID=C.RECID
  --LEFT JOIN TRANGLT9.DATA.DBO.HR_SME_'+@DATE_NS+' D
  LEFT JOIN [ANHCP].[MBO_SBO].[dbo].[HR_file_'+@DATE_NS+'] D
  ON C.DAO COLLATE DATABASE_DEFAULT =D.DAO 
  LEFT JOIN SERVER74.BICDATA.DBO.BRANCH_CODE E
  ON A.CO_CODE=E.BRANCH_ID
WHERE SAVE_DATE='''+@DATE1+''' 
  AND CATEGORY IN (''28103'',''28104'',''28113'',''28116'',''28109'',''28114'')
  AND VALUE_DATE BETWEEN '''+@DATE0+''' AND '''+@DATE1+''' 
  		AND A.CO_CODE NOT IN (SELECT BRANCH_CODE FROM ANHCP.CEO_PERFORMANCE.DBO.BRANCHOFF where branch_code <> ''VN0010289'')
	AND CUS_SEGMENT not in (''KHCN'',''HH'') ')

	--AND A.CO_CODE NOT IN (SELECT BRANCH_CODE FROM ANHCP.CEO_PERFORMANCE.DBO.BRANCHOFF)
	--AND CUS_SEGMENT<>''KHCN'' ')




print('2')				 --4.2.BAO LANH THANH TOAN
EXEC ('SELECT BUSINESS_DATE,MD_ID,CUST_ID,VALUE_DATE,MUC_DICH,CUS_SEGMENT,ORG_AMOUNT*MID_RATE AS AMOUNT_QD,
C.DAO AS DAO_CIF,D.[Tên nhân viên],BRANCH_NAME_SME,ZONE_ID_SME,CO_CODE,CATEGORY,FUND_PROGRAM INTO MD_B
  FROM SERVER16.[VPB_WHR2].[DBO].[MD_RPT_DAILY_VPB] A
  LEFT JOIN  SERVER16.VPB_WHR2.DBO.FOCURR_SAVE B
  ON A.CCY=B.CODE
  LEFT JOIN SERVER74.BICDATA.DBO.CUSTOMER C
  ON A.CUST_ID=C.RECID
  --LEFT JOIN TRANGLT9.DATA.DBO.HR_SME_'+@DATE_NS+' D
  LEFT JOIN [ANHCP].[MBO_SBO].[dbo].[HR_file_'+@DATE_NS+'] D
  ON C.DAO  COLLATE DATABASE_DEFAULT =D.DAO
  LEFT JOIN SERVER74.BICDATA.DBO.BRANCH_CODE E
  ON A.CO_CODE=E.BRANCH_ID
WHERE SAVE_DATE='''+@DATE1+''' 
  AND CATEGORY IN ( ''28102'')
  AND A.FUND_PROGRAM NOT LIKE ''%TAI TRO CHUOI HTC%'' AND A.FUND_PROGRAM NOT LIKE ''%VEAM''
  AND VALUE_DATE BETWEEN '''+@DATE0+''' AND '''+@DATE1+''' 
    		AND A.CO_CODE NOT IN (SELECT BRANCH_CODE FROM ANHCP.CEO_PERFORMANCE.DBO.BRANCHOFF where branch_code <> ''VN0010289'')
	AND CUS_SEGMENT not in (''KHCN'',''HH'') ')

	--AND A.CO_CODE NOT IN (SELECT BRANCH_CODE FROM ANHCP.CEO_PERFORMANCE.DBO.BRANCHOFF)
	--AND CUS_SEGMENT<>''KHCN'' ')

print('3')				 --4.3.BAO LANH 
  EXEC ('SELECT  BUSINESS_DATE,MD_ID,CUST_ID,VALUE_DATE,MUC_DICH,CUS_SEGMENT,ORG_AMOUNT*MID_RATE AS AMOUNT_QD,
  C.DAO AS DAO_CIF,D.[Tên nhân viên],BRANCH_NAME_SME,ZONE_ID_SME,CO_CODE,CATEGORY,FUND_PROGRAM INTO MD_C
  FROM SERVER16.[VPB_WHR2].[DBO].[MD_RPT_DAILY_VPB] A
  LEFT JOIN  SERVER16.VPB_WHR2.DBO.FOCURR_SAVE B
  ON A.CCY=B.CODE
  LEFT JOIN SERVER74.BICDATA.DBO.CUSTOMER C
  ON A.CUST_ID=C.RECID
  --LEFT JOIN TRANGLT9.DATA.DBO.HR_SME_'+@DATE_NS+' D -------- THAY BANG
  LEFT JOIN [ANHCP].[MBO_SBO].[dbo].[HR_file_'+@DATE_NS+'] D
  ON C.DAO COLLATE DATABASE_DEFAULT =D.DAO 
  LEFT JOIN SERVER74.BICDATA.DBO.BRANCH_CODE E
  ON A.CO_CODE=E.BRANCH_ID
WHERE SAVE_DATE='''+@DATE1+'''   
  AND CATEGORY IN (''28609'')
  AND (A.FUND_PROGRAM LIKE ''%TAI TRO CHUOI HTC%'' or A.FUND_PROGRAM  LIKE ''%VEAM'')
  AND VALUE_DATE BETWEEN '''+@DATE0+''' AND '''+@DATE1+''' 
      		AND A.CO_CODE NOT IN (SELECT BRANCH_CODE FROM ANHCP.CEO_PERFORMANCE.DBO.BRANCHOFF where branch_code <> ''VN0010289'')
	AND CUS_SEGMENT not in (''KHCN'',''HH'' )')

	--AND A.CO_CODE NOT IN (SELECT BRANCH_CODE FROM ANHCP.CEO_PERFORMANCE.DBO.BRANCHOFF)
	--AND CUS_SEGMENT<>''KHCN'' ')
 


 print('4')
 EXEC ('SELECT * INTO DOANH_SO_BAO_LANH_'+@DATE+'_NEW FROM ( SELECT * FROM MD_A
	UNION
	SELECT * FROM MD_B
	UNION 
 SELECT * FROM MD_C) D ') 
 
 print('5')
EXEC ('SELECT * ,ROW_NUMBER() OVER( PARTITION BY MD_ID ORDER BY BUSINESS_DATE) AS RN
INTO MD_D
FROM DOANH_SO_BAO_LANH_'+@DATE+'_NEW
ORDER BY BUSINESS_DATE')
print('6')
EXEC ('SELECT [BUSINESS_DATE]
      ,[MD_ID]
      ,[CUST_ID]
      ,[VALUE_DATE]
      ,[MUC_DICH]
      ,[CUS_SEGMENT]
      ,[AMOUNT_QD]/1E9  AS AMOUNT_QD
      ,[DAO_CIF]
      ,[Tên nhân viên]
      ,[BRANCH_NAME_SME]
      ,[ZONE_ID_SME]
      ,[CO_CODE]
      ,[CATEGORY]
      ,[FUND_PROGRAM]
      ,[RN] 
	   INTO MD_FINAL
FROM MD_D
WHERE RN=1 ')
--print('7')
--EXEC ('UPDATE MD_FINAL
--SET BUSINESS_DATE='''+@DATE1+''' ')

--SELECT * FROM #MD_FIN
--WHERE  FUND_PROGRAM<>'NONE'

--EXEC ('SELECT * INTO DOANH_SO_BAO_LANH FROM MD_FINAL')
--print('8')
--EXEC('SELECT *,ROW_NUMBER() OVER (PARTITION BY CUST_ID ORDER BY BUSINESS_DATE) AS COUNT_CIF 
--INTO MD_FINAL_1
-- FROM MD_FINAL')
--print('9')
--EXEC('UPDATE MD_FINAL_1
-- SET COUNT_CIF=0
-- WHERE COUNT_CIF>1')

--EXEC ('SELECT  *
--	  INTO DOANH_SO_BAO_LANH  
--	  FROM [TRADE].[dbo].MD_FINAL_1')
print('10')
EXEC('UPDATE MD_FINAL
SET ZONE_ID_SME=''CHUA PHAN BO''
WHERE  ZONE_ID_SME IS NULL OR ZONE_ID_SME =''NULL'' OR ZONE_ID_SME ='' ''')

EXEC ('INSERT DOANH_SO_BAO_LANH
SELECT * FROM MD_FINAL')


--drop table DOANH_SO_BAO_LANH
--SELECT * FROM DOANH_SO_BAO_LANH
--SELECT * FROM MD_FINAL order by value_date
--SELECT * INTO DOANH_SO_BAO_LANH FROM SERVER12.TRANGLT9.DBO.DOANH_SO_BAO_LANH
EXEC ('INSERT DOANH_SO_BAO_LANH
SELECT A.BUSINESS_DATE AS FROM_DATE,A.MD_ID,A.CUST_ID,A.VALUE_DATE,A.MUC_DICH,A.CUS_SEGMENT,A.AMOUNT_QD,A.DAO_CIF,A.[TÊN NHÂN VIÊN] AS STAFF_NAME,A.BRANCH_NAME_SME,A.ZONE_ID_SME,CO_CODE,A.CATEGORY,A.FUND_PROGRAM,A.RN,1, BUSINESS_DATE AS TO_DATE , left(VALUE_DATE, 7) as month, B.CUS_NAME
 FROM MD_FINAL A
left join [SERVER74].[BICDATA].[dbo].[CUSTOMER] b ON A.CUST_ID=B.RECID')

SELECT * FROM DOANH_SO_BAO_LANH ORDER BY DAO_CIF
